package com.myres.hotitem.db;

import com.myres.db.ConnectionPool;
import com.myres.model.Category;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.myres.model.Product;

import java.sql.Timestamp;

import com.myres.util.TimeTool;
public class HotItemService {
     public List<Product> getHotItems(){
    	 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement(
    				 "select " +
    				 "   _product_id,"+
    			      "  sum(_amount) as c,"+
    			      " (select _name from res_products where _id=_product_id)," +
    			      " (select _price from res_products where _id=_product_id)"+
    			      " from res_order_items "+     
    			      " where res_order_items._order_id in ("+
    			      "    select _id FROM res_orders where res_orders._order_time>?) "+
    			      " GROUP by _product_id   "+
    			      " order by c desc  "+     
    			      " limit 10 ");
    		 pstmt.setTimestamp(1, TimeTool.String2Timestamp("yyyy-MM-dd", TimeTool.time2String("yyyy-MM-dd", new Timestamp(System.currentTimeMillis()-1000*60*60*24*2))));
    		 rs=pstmt.executeQuery();
    		 return this.populate(rs);
    	 }catch(Exception e){
    		 System.out.print("ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
     }
     private List<Product> populate(ResultSet rs) throws SQLException {
    	 List<Product> l=new ArrayList<Product>();
    	 while(rs.next()){
    		 Product p=new Product();
    		 p.setId(rs.getInt(1));
    		 p.setName(rs.getString(3));
    		 p.setPrice(rs.getFloat(4));
    		 l.add(p);
    	 }
    	 System.out.println("rank number="+l.size());
    	 return l;	 
     }
}

